/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.Interview;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import utility.DatabaseConnection;

/**
 *
 * @author Admin
 */
public class InterviewModel {

    private String sql = null;
    private Connection con = null;
    private ResultSet rs = null;
    private PreparedStatement ps = null;

    public InterviewModel() {
    }

    public boolean insertInterview(String vid, String aid, String date, String status) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "insert into Interview (VacancyID, ApplicantID, AttachedDate, Status) values (?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, vid);
            ps.setString(2, aid);
            ps.setString(3, date);
            ps.setString(4, status);

            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public ArrayList<Interview> getSelectedInterview() {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Interview getInterviewByAidAndVid(String aid, String vid) {
        Interview i = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyTitle, a.ApplicantName, i.* "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where i.VacancyID =? and i.ApplicantID =?";
            ps = con.prepareStatement(sql);
            ps.setString(1, vid);
            ps.setString(2, aid);
            rs = ps.executeQuery();
            while (rs.next()) {
                i = new Interview();
                i.setVacancyTitle(rs.getString(1));
                i.setApplicantName(rs.getString(2));
                i.setVacancyid(rs.getString(3));
                i.setApplicantid(rs.getString(4));
                i.setEmpid(rs.getString(5));
                i.setScheduleDate(rs.getString(6));
                i.setAttachedDate(rs.getString(7));
                i.setStartTime(rs.getString(8));
                i.setEndTime(rs.getString(9));
                i.setCommand(rs.getString(10));
                i.setStatus(rs.getString(11));
            }
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean updateInterview(Interview i) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "update Interview set EmpID =?, ScheduleDate =?, StartTime =?, EndTime =?, Command=?, Status =? "
                    + "where VacancyID =? and ApplicantID =? ";
            ps = con.prepareStatement(sql);
            ps.setString(1, i.getEmpid());
            ps.setString(2, i.getScheduleDate());
            ps.setString(3, i.getStartTime());
            ps.setString(4, i.getEndTime());
            ps.setString(5, i.getCommand());
            ps.setString(6, i.getStatus());
            ps.setString(7, i.getVacancyid());
            ps.setString(8, i.getApplicantid());

            if (ps.executeUpdate() > 0) {
                return true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return false;
    }

    public ArrayList<Interview> getInterviewbyStatus(String status) {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where i.Status=? "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            ps.setString(1, status);
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Interview> getInterviewbyVacancyName(String vacancy) {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where v.VacancyTitle like ? "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + vacancy + "%");
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Interview> getInterviewbyApplicantName(String applicant) {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where a.ApplicantName like ? "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + applicant + "%");
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Interview> getInterviewbyEmpID(String id) {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where i.EmpID=? "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int getStatusItv(String id){
        int num = 0;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select count(Status) from Interview where VacancyID = ? and Status = 'Selected' ";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                num = Integer.parseInt(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    public ArrayList<Interview> getInterviewbyVid(String status, String vid) {
        ArrayList<Interview> lst = new ArrayList<Interview>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select v.VacancyID, a.ApplicantID, v.VacancyTitle, a.ApplicantName, i.AttachedDate, i.Status, v.DepartmentID "
                    + "from Vacancy v inner join Interview i "
                    + "on v.VacancyID = i.VacancyID "
                    + "inner join Applicant a "
                    + "on a.ApplicantID = i.ApplicantID "
                    + "where i.Status=? and v.VacancyID =? "
                    + "order by i.AttachedDate desc ";
            ps = con.prepareStatement(sql);
            ps.setString(1, status);
            ps.setString(2, vid);
            rs = ps.executeQuery();

            while (rs.next()) {
                Interview i = new Interview();
                i.setVacancyid(rs.getString(1));
                i.setApplicantid(rs.getString(2));
                i.setVacancyTitle(rs.getString(3));
                i.setApplicantName(rs.getString(4));
                i.setAttachedDate(rs.getString(5));
                i.setStatus(rs.getString(6));
                i.setDepartmentid(rs.getString(7));
                lst.add(i);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
